﻿using Domain.Entity.bs;
using Domain.Entity;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Authorization;
using Application.ProductTypeService;
using OfficeOpenXml;
using Application.ProductService;
using Dapper;
using System.Data.SqlClient;

namespace WMSManage.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    [Authorize]//需要鉴权
    public class ProductTypeController : ControllerBase
    {
        private readonly IProductTypeService productTypeService;

        public ProductTypeController(IProductTypeService productTypeService)
        {
            this.productTypeService = productTypeService;
        }


        /// <summary>
        /// 分页查询货品类型
        /// </summary>
        /// <param name="name">类型名</param>
        /// <param name="status">状态</param>
        /// <param name="createname">创建人</param>
        /// <param name="starttime">开始时间</param>
        /// <param name="endtime">结束时间</param>
        /// <param name="CurrentPage">当前页</param>
        /// <returns></returns>
        [HttpGet]
        public async Task<FenYe<ProductType>> GetProductType(string? name, int? status, string? createname, string? starttime, string? endtime, int CurrentPage)
        {
            FenYe<ProductType> fenYe = new FenYe<ProductType>()
            {
                CurrentPage = CurrentPage,
            };
            return await productTypeService.GetProductType(fenYe, name, status, createname, starttime, endtime);
        }



        /// <summary>
        /// 查询所有货品类型
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public async Task<List<ProductType>> GetAllProductTypes()
        {
            return await productTypeService.GetAllProductTypes();
        }


        /// <summary>
        /// 添加货品类型
        /// </summary>
        /// <param name="addProductTypeDto"></param>
        /// <returns></returns>
        [HttpPost]
        public async Task<int> AddProductType(AddProductTypeDto addProductTypeDto)
        {
            return await productTypeService.AddProductType(addProductTypeDto);
        }



        /// <summary>
        /// 修改货品类型
        /// </summary>
        /// <param name="updateProductTypeDto"></param>
        /// <returns></returns>
        [HttpPut]
        public async Task<int> UpdateProductType(UpdateProductTypeDto updateProductTypeDto)
        {
            return await productTypeService.UpdateProductType(updateProductTypeDto);
        }


        /// <summary>
        /// 删除货品类型
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [HttpPut]
        public async Task<int> DeleteProductType(int id)
        {
            return await productTypeService.DeleteProductType(id);
        }



        /// <summary>
        /// 根据id查询货品类型
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [HttpGet]
        public async Task<ProductType> GetProductTypeById(int id)
        {
            return await productTypeService.GetProductTypeById(id);
        }



        /// <summary>
        /// 导入货品类型
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public async Task<IActionResult> ImportProductTypes()
        {
            //检查有没有文件
            var file = HttpContext.Request.Form.Files["file"];
            if (file == null || file.Length == 0)
            {
                return BadRequest(new { message = "未上传文件" });
            }

            try
            {
                using var stream = new MemoryStream();
                await file.CopyToAsync(stream);

                using var package = new ExcelPackage(stream);
                var worksheet = package.Workbook.Worksheets[0];

                if (worksheet.Dimension == null)
                {
                    return BadRequest(new { message = "Excel文件没有数据" });
                }

                int rowCount = worksheet.Dimension.Rows;
                int colCount = worksheet.Dimension.Columns;

                // 读取表头
                var headers = Enumerable.Range(1, colCount)
                    .Select(col => worksheet.Cells[1, col].Value?.ToString()?.Trim().ToLower())
                    .ToList();

                // 验证必要列是否存在
                if (!headers.Contains("货品类型编号"))
                {
                    return BadRequest(new { message = "Excel缺少'货品类型编号'列" });
                }

                // 获取所有已存在的货品类型编号和名称
                var existingProducts = await productTypeService.GetAllProductTypes();//获取所有货品类型
                var existingCodes = existingProducts.Select(x => x.type_code).ToList();//所有编号
                var existingNames = existingProducts.Select(x => x.type_name).ToList();//所有名字

                var duplicateRows = new List<int>();
                var successCount = 0;
                var errorMessages = new List<string>();

                // 读取数据行
                for (int row = 2; row <= rowCount; row++)
                {
                    var rowData = new AddProductTypeDto();
                    bool hasError = false;

                    for (int col = 1; col <= colCount; col++)
                    {
                        var header = headers[col - 1];
                        var value = worksheet.Cells[row, col].Value?.ToString()?.Trim();

                        try
                        {
                            // 赋值逻辑
                            switch (header)
                            {
                                case "货品类型编号":
                                    rowData.type_code = value;
                                    break;
                                case "货品类型名称":
                                    rowData.type_name = value;
                                    break;
                                case "父级id":
                                    rowData.type_pid = Convert.ToInt32(value);
                                    break;
                                case "排序":
                                    rowData.type_sort = Convert.ToInt32(value);
                                    break;
                                case "状态":
                                    rowData.type_status = Convert.ToInt32(value);
                                    break;
                                case "创建人":
                                    rowData.create_name = value;
                                    break;
                                case "创建时间":
                                    rowData.create_time = value;
                                    break;
                                case "备注":
                                    rowData.remark = value;
                                    break;
                            }
                        }
                        catch (Exception ex)
                        {
                            errorMessages.Add($"第{row}行数据格式错误: {ex.Message}");
                            hasError = true;
                            break;
                        }
                    }

                    if (hasError) continue;

                    // 检查是否已存在（更严格的检查）
                    if (existingCodes.Contains(rowData.type_code))
                    {
                        duplicateRows.Add(row);
                        errorMessages.Add($"第{row}行数据导入失败: 货品类型编号 {rowData.type_code} 已存在");
                        continue;
                    }

                    // 检查名称是否重复（如果需要）
                    if (existingNames.Contains(rowData.type_name))
                    {
                        duplicateRows.Add(row);
                        errorMessages.Add($"第{row}行数据导入失败: 货品类型名称 {rowData.type_name} 已存在");
                        continue;
                    }

                    var result = await productTypeService.AddProductType(rowData);
                    if (result != 1)
                    {
                        errorMessages.Add($"第{row}行数据导入失败");
                    }
                    else
                    {
                        successCount++;
                        existingCodes.Add(rowData.type_code); // 更新缓存
                        existingNames.Add(rowData.type_name); // 更新缓存
                    }
                }

                return Ok(new
                {
                    successCount,
                    duplicateCount = duplicateRows.Count,
                    duplicateRows,
                    errors = errorMessages
                });
            }
            catch (Exception ex)
            {
                return StatusCode(500, new { message = "处理文件时出错", error = ex.Message });
            }
        }



        /// <summary>
        /// 删除前检查
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [HttpGet]
        public IActionResult CheckUsage(int id)
        {
            try
            {
                // 为了提升效率，我们将两次检查合并为一条SQL语句，以减少数据库的往返次数。
                // EXISTS 在找到第一条匹配记录后就会立即返回，性能优于 COUNT。
                //连接字符串
                string con = "Data Source=121.37.93.71;Initial Catalog=CSSL_ZCTWMS;User ID=sa;Password=geili@2025;Encrypt=False";
                using(SqlConnection sqlConnection=new SqlConnection(con))
                {
                    const string sql = @"
                    SELECT 
                        CAST(CASE WHEN EXISTS (SELECT 1 FROM bs_product_type WHERE type_pid = @TypeId) THEN 1 ELSE 0 END AS BIT) AS HasChildren,
                        CAST(CASE WHEN EXISTS (SELECT 1 FROM bs_product WHERE type_id = @TypeId) THEN 1 ELSE 0 END AS BIT) AS HasProducts;";

                    var parameters = new { TypeId = id };

                    // 使用 Dapper 的 QueryFirstOrDefault 来执行查询
                    var result = sqlConnection.QueryFirstOrDefault(sql, parameters);

                    if (result == null)
                    {
                        // 如果查询没有返回任何结果（理论上不应发生），则返回默认的安全值
                        return Ok(new { hasChildren = false, hasProducts = false });
                    }

                    // 返回一个包含两个布尔值的匿名对象，前端可以直接使用
                    return Ok(new { hasChildren = (bool)result.HasChildren, hasProducts = (bool)result.HasProducts });
                }
            }
            catch (Exception ex)
            {
                // 在实际生产环境中，建议使用更完善的日志记录
                Console.WriteLine($"Error in CheckUsage: {ex.Message}");
                return StatusCode(500, "检查类型使用情况时发生内部错误。");
            }
        }
    }
}
